---
title: "pk73094_policy_data stored procedure documentation"
summary: "Analysis of the pk73094_policy_data Oracle package including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "pk73094_policy_data"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk73094_policy_data"
retrieval_date: "2026-08-31"
---

# pk73094_policy_data

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends oracle query --name pk73094_policy_data` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicyData` |

## Purpose

`pk73094_policy_data` supplies the primary policy-header retrieval for policy view data objects. This
library calls its `getpolicy` member; the package also exposes `get_account_name`, which is not invoked
by this library.

## Procedures / Methods

### getpolicy

| Field | Value |
| --- | --- |
| **Returns** | `policy_cur` (IN OUT) |
| **Called by** | `clsPolicyData.vb:2297` (`RetrieveSQL`) |

**Description:** Opens the policy header cursor, including derived status and cancel status. Monetary
header values are null-safe, cancel status is always the shared derivation (never a stored column),
and SIC code is deliberately not returned by this view (see rules PR-BVP-017, PR-BVP-018, PR-BVP-019 in
[Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `apolicynum` | IN | `NUMBER` | — | Policy number |
| `recordset` | IN OUT | `policy_cur` | — | Policy header result set |

### get_account_name

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` (function return value) |
| **Called by** | not called by this library — exposed member documented for completeness |

**Description:** Returns the "best" policy's legal name for an account number, chosen by a fixed
ranking (see rules PR-BVP-020, PR-BVP-021 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_account_num` | IN | `VARCHAR2` | — | Account number |
| *(return)* | RETURN | `VARCHAR2` | — | Best policy's `legal_name`, or NULL if `in_account_num` is NULL |

## Business Rules

Rules extracted from this package's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-017 | Monetary header values are null-safe | `current_balance` / `premium_estimate` may be NULL | Return `NVL(current_balance,0)` and `NVL(premium_estimate,0)` |
| PR-BVP-018 | Cancel status on the policy header is always the shared derivation, never a stored column | always | Select `sp72268_get_cancel_status(p.policy_num)` as `policy_cancel_status` |
| PR-BVP-019 | SIC code is deliberately not returned by this view | always | Project `null as sic_code` |
| PR-BVP-020 | The "best" policy for an account is chosen by a fixed ranking | multiple policies share an account number | Order by status rank (`'0','3'`→1, `'5'`→2, else 3), then self-referencing-relationship first, then `cancel_date DESC NULLS LAST`, then `policy_num`; return that policy's `legal_name` |
| PR-BVP-021 | A null account number yields no name | `in_account_num IS NULL` | Return NULL |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this package:

| Operation | Description | Trigger |
| --- | --- | --- |
| Retrieve policy header | Opens the policy header cursor including derived status and cancel status | `clsPolicyData` policy retrieval (`clsPolicyData.vb:2297`) |
| Resolve account name | Returns the legal name of the best policy for an account number | not invoked by this library |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
PROCEDURE getpolicy (apolicynum IN     NUMBER,
                     recordset  IN OUT policy_cur);

FUNCTION  get_account_name (in_account_num IN VARCHAR2)
   RETURN VARCHAR2;
```

</details>

<details>
<summary>PL/SQL package body (rule-bearing excerpt)</summary>

```sql
-- getpolicy: derived header columns
SELECT ...
       NVL (p.current_balance, 0)                      current_balance,
       NVL (p.premium_estimate, 0)                     premium_estimate,
       psc.code_descr                                  status,
       psc.policy_status_group_code,
       sp72268_get_cancel_status (p.policy_num)        policy_cancel_status,
       NULL                                            AS sic_code
  FROM policy p
  JOIN policy_status_code psc
    ON p.policy_status_code = psc.code
 WHERE p.policy_num = apolicynum;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: `WCIS_DBA.pk73094_policy_data`
- Legends CLI query executed 2026-08-31: `legends oracle query --name pk73094_policy_data`
- Calling code: `SAIF.Business.View.Policy/clsPolicyData.vb:2297`